package DAO;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import object.ExpressBranchList;
import object.ExpressList;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Repository;

import POJO.Express;


@Repository
public class ExpressDao {
	@Autowired
	JdbcTemplate jdbcTemplate;
	
	ArrayList<ExpressList> expressLists = null;
	ArrayList<ExpressBranchList> expressBranchLists = null;
	
	/**
	 * 查询代理商快递网点列表
	 * @param agentId
	 * @param companyID
	 * @return
	 * by zgy
	 */
	public ArrayList<ExpressBranchList> getExpressBranchListByAgentId(int agentId,int companyID){
		expressBranchLists=new ArrayList<ExpressBranchList>();
		String sql = "select express_id,express_name,express_addr from express e where e.express_company_id = ? and e.express_agent_id = ? and e.express_status = 0 order by e.express_id;";
		Object[] params=new Object[]{companyID,agentId};
		jdbcTemplate.query(sql,params, new RowCallbackHandler(){
			public void processRow(ResultSet rs) throws SQLException {
				ExpressBranchList expressBranchList = new ExpressBranchList();
				expressBranchList.setExpressBranchId(String.valueOf(rs.getInt("express_id")));
				expressBranchList.setExpressBranchName(rs.getString("express_name"));
				expressBranchList.setExpressBranchAddr(rs.getString("express_addr"));
				expressBranchLists.add(expressBranchList);
			}
		});
		return expressBranchLists;
	}
	
	/**
	 * 查询代理商快递公司列表
	 * @param agentId
	 * @return
	 * by zgy
	 */
	public ArrayList<ExpressList> getExpressListByAgentId(int agentId){
		expressLists=new ArrayList<ExpressList>();
		String sql = "select distinct c.company_id,c.company_name from company c,express e where c.company_id = e.express_company_id and e.express_agent_id = ? order by c.company_id;";
		Object[] params=new Object[]{agentId};
		jdbcTemplate.query(sql,params, new RowCallbackHandler(){
			public void processRow(ResultSet rs) throws SQLException {
				ExpressList expressList = new ExpressList();
				expressList.setExpressCompanyId(String.valueOf(rs.getInt("company_id")));
				expressList.setExpressCompanyName(rs.getString("company_name"));
				expressLists.add(expressList);
			}
		});
		return expressLists;
	}
	
	/**
	 * 根据指定快递网点id返回扣费类型
	 * @param expressId
	 * @return 失败返回-1
	 */
	public int getPayWay(int expressId){
		String getPayWaySql="SELECT express_payway FROM express WHERE express_id=?;";
		Object[] params=new Object[]{expressId};
		int result=-1;
		try {
			result=jdbcTemplate.queryForObject(getPayWaySql,params,Integer.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public Express getExpressById(int expressId){
		final Express express=new Express();
		String getExpressByIdSql="select * from express where express_id=?";
		Object[] params=new Object[]{expressId};
		jdbcTemplate.query(getExpressByIdSql,params, new RowCallbackHandler(){
			public void processRow(ResultSet rs) throws SQLException {
				express.setId(rs.getInt("express_id"));
				express.setCost(rs.getDouble("express_cost"));
				express.setBalance(rs.getDouble("express_balance"));
				express.setName(rs.getString("express_name"));
				express.setOverdueBalance(rs.getDouble("express_overdue_balance"));
				express.setPayWay(rs.getInt("express_payway"));
				express.setStatus(rs.getInt("express_status"));
				express.setHotLine(rs.getString("express_hotLine"));
			}
		});		
		
		if(express.getId()==-1){
			return null;
		}else{
			return express;
		}		
	}

	/**
	 * 更新余额
	 * @param express
	 * @return
	 */
	public boolean updateBalance(Express express) {
		String updateBalanceSql="UPDATE `express` SET `express_balance`=? WHERE `express_id`=?;";
		Object[] params=new Object[]{express.getBalance(),express.getId()};
		int result=jdbcTemplate.update(updateBalanceSql,params);
		if(result!=0){
			return true;
		}else{
			return false;
		}
	}
	
	/**
	 * 更新余额
	 * @param express
	 * @return
	 */
	public boolean updateState(Express express) {
		String updateBalanceSql="UPDATE `express` SET `express_status`=? WHERE `express_id`=?;";
		Object[] params=new Object[]{express.getStatus(),express.getId()};
		int result=jdbcTemplate.update(updateBalanceSql,params);
		if(result!=0){
			return true;
		}else{
			return false;
		}
	}
	
}
